﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Office2010.Excel;
using DocumentFormat.OpenXml.Office2010.ExcelAc;
using DocumentFormat.OpenXml.Presentation;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using Hangfire.LiteDB;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.FastDBF;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Repository.Extensions;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.WebUtilities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Net.Http.Headers;
using MySql.Data.MySqlClient;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using NetTopologySuite.Mathematics;
using Newtonsoft.Json;
using Npgsql;
using NPOI.POIFS.Properties;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Quartz.Util;
using Serilog.Parsing;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Drawing.Printing;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Transactions;
using static housingsafety.server.Controllers.VillageMngController;
using static NPOI.HSSF.Util.HSSFColor;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 村庄管理服务
    /// </summary>
    public class CheckTaskController : BaseController<VillageMngController>
    {
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsRepository;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;

        public CheckTaskController(IStorageProvider storageProvider
            , IRepository<ZmUser> userRepository
            , IRepository<ZmBuildingsLayer> buildingsRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , ILogger<VillageMngController> logger
            , IRepository<ZmSystemLog> systemLogRepository) : base(logger, systemLogRepository)
        {
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();

            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _fileAttachRepository = fileAttachRepository;
            _villageFireSafetyRepository = villageFireSafetyRepository;
        }
        /// <summary>
        /// 获取本年度所有轮次  
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getYearRound")]
        public RespRet<Page<object>> getYearRound()
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            string year = DateTime.Now.Year.ToString();
            string sql = "select * from zm_taskpc where year='" + year + "'";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = list.Count; //总数量
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, PageTotal = count, Total = count });

        }




        /// <summary>
        /// 获取本年度当前轮次  
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getYearRound2")]
        public RespRet<Page<object>> getYearRound2()
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            string year = DateTime.Now.Year.ToString();
            string sql = "select * from (select ROW_NUMBER() OVER(PARTITION BY year ORDER BY createon DESC) AS row_num,pch from zm_taskpc where year='" + year + "') t1 where  row_num=1";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

            int count = list.Count; //总数量
            if (count == 0)
            {
                string pch = "第1轮";
                string sql2 = " INSERT INTO public.zm_taskpc " +
                            "(id,  year, pch, createon) VALUES" +
                            " ('" + Guid.NewGuid().ToString("N") + "', '" + year + "', '" + pch + "','" + DateTime.Now + "');";
                count = _dbContext.Database.ExecuteSqlRaw(sql2);

                sql = "select * from (select ROW_NUMBER() OVER(PARTITION BY year ORDER BY createon DESC) AS row_num,pch from zm_taskpc where year='" + year + "') t1 where  row_num=1";
                list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            }

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 获取本年度当前轮次  没有该年度的则需要新建
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getNewRound")]
        public RespRet<Page<object>> getNewRound([FromQuery] string pch)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string year = DateTime.Now.Year.ToString();
            int value = Convert.ToInt32(pch.Replace("第", "").Replace("轮", "").ToString());

            string newpch = "第" + (value + 1) + "轮";
            string sql2 = " INSERT INTO public.zm_taskpc " +
                        "(id,  year, pch, createon) VALUES" +
                        " ('" + Guid.NewGuid().ToString("N") + "', '" + year + "', '" + newpch + "','" + DateTime.Now + "');";
            int count = _dbContext.Database.ExecuteSqlRaw(sql2);
            return new RespRet<Page<object>>(true, "ok", new Page<object> { PageTotal = count, Total = count });
        }

        /// <summary>
        /// 获取当前轮次 未分配的组
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getZulist")]
        public RespRet<Page<object>> getZulist([FromQuery] string pch)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            string year = DateTime.Now.Year.ToString();
            //原始分类到组
            //string sql = @"select zname,CONCAT(zname,'(',unitname,'-',nickname,')') AS fullname from ( select zname,substring(unitname FROM '.*?消防救援支队' ) unitname,string_agg(nickname, '、') nickname   from  zm_task_zuser  where zname not in  (SELECT  unnest(string_to_array(zname, ',')) AS zname
            //       FROM zm_task where pch = '" + pch+"' and year = '"+year+"')  group by zname,substring(unitname FROM '.*?消防救援支队' )) t2";

            string sql = @"select number,CONCAT(nickname,'(',yfp,'-',unitname,')')  fullname   from (select t1.*,COALESCE(t2.count,0) yfp from (SELECT
		zname,number,SUBSTRING ( unitname FROM '.*?消防救援支队' ) unitname,nickname FROM zm_task_zuser )  t1 left join (select number,count(fwbh) count from zm_taskdetail where year='"+year+ "' and pch='" + pch+"'  GROUP BY number) t2 on t1.number=t2.number  order by t2.count asc	) t3";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

            int count = list.Count; //总数量
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, PageTotal = count, Total = count });
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/tasklist")]
        public RespRet<Page<object>> tasklist([FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string txtKeyWord, [FromQuery] string pch)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
            string sql = "";

            string strWhere = " ";
            if (model != null)
            {
                if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
            }


            //if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            //{
            //    startTime = "" + startTime + "";
            //    endTime = DateTime.Now.ToString("yyyy-MM-dd");
            //}
            string year = DateTime.Now.Year.ToString();
            string sql_count = @$"select count(1) cnt from  zm_task t where 1=1  "+strWhere;
            if (!string.IsNullOrEmpty(txtKeyWord) && !string.IsNullOrEmpty(pch))
            {
                sql = "select ROW_NUMBER() OVER()index,t1.*,t2.region_name xzq,t3.region_name town,t4.region_name village,t5.count cqcount1,t5.villagecount,COALESCE(t5.hycount,0) hycount,COALESCE(t5.whycount,0) whycount,CONCAT(t2.region_name, t3.region_name, t4.region_name) AS full_region,TO_CHAR(t1.create_on, 'YYYY-MM-DD HH24:MI:SS') time,t6.ydb,t6.wdb,concat( CASE WHEN t6.ydb = 0   THEN 0.00 WHEN t5.hycount = 0   THEN 0.00   WHEN round(t6.ydb::numeric / t5.hycount::numeric * 100, 2) > 100 THEN 100.00  ELSE round(t6.ydb::numeric / t5.hycount::numeric * 100, 2)  END, '%') AS dbl,\tconcat (\r\n\tCASE\r\n\t\t\t\r\n\t\t\tWHEN t6.wdb = 0 THEN\r\n\t\t\t0.00 \r\n\t\t\tWHEN t5.hycount = 0 THEN\r\n\t\t\t0.00 \r\n\t\t\tWHEN round( t6.wdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) > 100 THEN\r\n\t\t\t100.00 ELSE round( t6.wdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) \r\n\t\tEND,\r\n\t\t'%' \r\n\t) AS wdbl,t6.qtwdb,concat (CASE WHEN t6.qtwdb = 0 THEN 0.00 WHEN t5.hycount = 0 THEN 0.00  WHEN round( t6.qtwdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) > 100 THEN 100.00 ELSE round( t6.qtwdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) END,'%' ) AS qtwdbl   from " +
                    "(select * from  zm_task   t where year='"+ year + "'  and taskname like '%" + txtKeyWord + "%' and pch='"+pch+"'"+strWhere+"    order by create_on  desc  limit " + pageSize + " offset (" + pageIndex + " - 1) * " + pageSize + " ) t1 " +
                    "left join zm_department t2 on t1.xzqdm=(t2.id) " +
                    "left join zm_department t3 on concat(t1.towndm,'000')=(t3.id)" +
                    "left join zm_department t4 on concat(t1.villagedm)=(t4.id) " +
                    "left join (select t1.taskid,COALESCE(sum(case when t2.jsrysstd='是' then 1 else 0 END),0) ydb,COALESCE(sum(case when t2.jsrysstd='否' then 1 else 0 END),0) wdb,COALESCE ( SUM ( CASE WHEN t2.wdwhjgfjgqsyyrkrgbdj = 1    or t3.sfczckfhwggptzl = '1'   or t3.use_electric_bicycle=1 \r\n   or t3.use_bottled_liquefied_gas=1  or t3.use_stacked_items ='1'  THEN 1 ELSE 0 END ), 0 ) qtwdb  from zm_taskdetail  t1 left join   zm_pc_baselog_sqjz t2 ON t1.fwbh = t2.fwbh  and t1.baseid=t2.id and t1.ybhnumber=t2.create_by  LEFT JOIN zm_pc_yhlog_sqjz t3 ON t1.fwbh = t3.fwbh  AND t1.baseid = t3.base_id  AND t1.ybhnumber = t3.create_by   GROUP BY  taskid) t6 on t1.id=(t6.taskid)  " +
                    "left join (select count(fwbh) count,taskid,count(distinct(villagedm)) villagecount,sum(case when hyzt='1' then 1 else 0 end ) hycount,sum(case when hyzt is null  then 1 else 0 end ) whycount from  zm_taskdetail group by taskid) t5 on t1.id=t5.taskid";

                sql_count += " and taskname like '%" + txtKeyWord + "%' and pch='"+pch+ "'" + strWhere;
            }
            else if (string.IsNullOrEmpty(txtKeyWord) && string.IsNullOrEmpty(pch))
            {
                sql = "select ROW_NUMBER() OVER()index,t1.*,t2.region_name xzq,t3.region_name town,t4.region_name village,t5.count cqcount1,t5.villagecount,COALESCE(t5.hycount,0) hycount,COALESCE(t5.whycount,0) whycount,CONCAT(t2.region_name, t3.region_name, t4.region_name) AS full_region,TO_CHAR(t1.create_on, 'YYYY-MM-DD HH24:MI:SS') time,t6.ydb,t6.wdb,concat(CASE WHEN t6.ydb = 0   THEN 0.00 WHEN t5.hycount = 0   THEN 0.00  WHEN round(t6.ydb::numeric / t5.hycount::numeric * 100, 2) > 100 THEN 100.00   ELSE round(t6.ydb::numeric / t5.hycount::numeric * 100, 2)   END, '%') AS dbl,\tconcat (\r\n\tCASE\r\n\t\t\t\r\n\t\t\tWHEN t6.wdb = 0 THEN\r\n\t\t\t0.00 \r\n\t\t\tWHEN t5.hycount = 0 THEN\r\n\t\t\t0.00 \r\n\t\t\tWHEN round( t6.wdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) > 100 THEN\r\n\t\t\t100.00 ELSE round( t6.wdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) \r\n\t\tEND,\r\n\t\t'%' \r\n\t) AS wdbl,t6.qtwdb,concat (CASE WHEN t6.qtwdb = 0 THEN 0.00 WHEN t5.hycount = 0 THEN 0.00  WHEN round( t6.qtwdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) > 100 THEN 100.00 ELSE round( t6.qtwdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) END,'%' ) AS qtwdbl  from " +
                    "(select * from  zm_task   t where year='"+ year + "'"+strWhere+"     order by create_on  desc   limit " + pageSize + " offset (" + pageIndex + " - 1) * " + pageSize + ") t1 " +
                    "left join zm_department t2 on t1.xzqdm=(t2.id) " +
                    "left join zm_department t3 on concat(t1.towndm,'000')=(t3.id)" +
                    "left join zm_department t4 on concat(t1.villagedm)=(t4.id) " +
                    "left join (select t1.taskid,COALESCE(sum(case when t2.jsrysstd='是' then 1 else 0 END),0) ydb,COALESCE(sum(case when t2.jsrysstd='否' then 1 else 0 END),0) wdb,COALESCE ( SUM ( CASE WHEN t2.wdwhjgfjgqsyyrkrgbdj = 1    or t3.sfczckfhwggptzl = '1'   or t3.use_electric_bicycle=1 \r\n   or t3.use_bottled_liquefied_gas=1  or t3.use_stacked_items ='1'  THEN 1 ELSE 0 END ), 0 ) qtwdb  from zm_taskdetail  t1 left join    zm_pc_baselog_sqjz t2 ON t1.fwbh = t2.fwbh  and t1.baseid=t2.id and t1.ybhnumber=t2.create_by  LEFT JOIN zm_pc_yhlog_sqjz t3 ON t1.fwbh = t3.fwbh  AND t1.baseid = t3.base_id  AND t1.ybhnumber = t3.create_by  GROUP BY  taskid) t6 on t1.id=(t6.taskid)  " +
                    "left join (select count(fwbh) count,taskid,count(distinct(villagedm)) villagecount,sum(case when hyzt='1' then 1 else 0 end ) hycount,sum(case when hyzt is null  then 1 else 0 end ) whycount from  zm_taskdetail group by taskid) t5 on t1.id=t5.taskid";
            }
            else  if (!string.IsNullOrEmpty(txtKeyWord) &&string.IsNullOrEmpty(pch))
            {
                sql = "select ROW_NUMBER() OVER()index,t1.*,t2.region_name xzq,t3.region_name town,t4.region_name village,t5.count cqcount1,t5.villagecount,COALESCE(t5.hycount,0) hycount,COALESCE(t5.whycount,0) whycount,CONCAT(t2.region_name, t3.region_name, t4.region_name) AS full_region,TO_CHAR(t1.create_on, 'YYYY-MM-DD HH24:MI:SS') time,t6.ydb,t6.wdb,concat(\r\n                \r\n\t\t\t\t\t\t\t\t  CASE WHEN t6.ydb = 0   THEN 0.00\r\n\t\t\t\t\t\t\t\t\tWHEN t5.hycount = 0   THEN 0.00\r\n                  WHEN round(t6.ydb::numeric / t5.hycount::numeric * 100, 2) > 100 THEN 100.00\r\n                  ELSE round(t6.ydb::numeric / t5.hycount::numeric * 100, 2)\r\n                END, '%') AS dbl,\tconcat (\r\n\tCASE\r\n\t\t\t\r\n\t\t\tWHEN t6.wdb = 0 THEN\r\n\t\t\t0.00 \r\n\t\t\tWHEN t5.hycount = 0 THEN\r\n\t\t\t0.00 \r\n\t\t\tWHEN round( t6.wdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) > 100 THEN\r\n\t\t\t100.00 ELSE round( t6.wdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) \r\n\t\tEND,\r\n\t\t'%' \r\n\t) AS wdbl,t6.qtwdb,concat (CASE WHEN t6.qtwdb = 0 THEN 0.00 WHEN t5.hycount = 0 THEN 0.00  WHEN round( t6.qtwdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) > 100 THEN 100.00 ELSE round( t6.qtwdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) END,'%' ) AS qtwdbl  from " +
                    "(select * from  zm_task   t where year='"+ year + "'   and taskname like '%" + txtKeyWord + "%'"+strWhere+"    order by create_on  desc  limit " + pageSize + " offset (" + pageIndex + " - 1) * " + pageSize + " ) t1 " +
                    "left join zm_department t2 on t1.xzqdm=(t2.id) " +
                     "left join zm_department t3 on concat(t1.towndm,'000')=(t3.id)" +
                    "left join zm_department t4 on concat(t1.villagedm)=(t4.id) " +
                     "left join (select t1.taskid,COALESCE(sum(case when t2.jsrysstd='是' then 1 else 0 END),0) ydb,COALESCE(sum(case when t2.jsrysstd='否' then 1 else 0 END),0) wdb,COALESCE ( SUM ( CASE WHEN t2.wdwhjgfjgqsyyrkrgbdj = 1    or t3.sfczckfhwggptzl = '1'   or t3.use_electric_bicycle=1 \r\n   or t3.use_bottled_liquefied_gas=1  or t3.use_stacked_items ='1'  THEN 1 ELSE 0 END ), 0 ) qtwdb  from zm_taskdetail  t1 left join   zm_pc_baselog_sqjz t2 ON t1.fwbh = t2.fwbh  and t1.baseid=t2.id and t1.ybhnumber=t2.create_by  LEFT JOIN zm_pc_yhlog_sqjz t3 ON t1.fwbh = t3.fwbh  AND t1.baseid = t3.base_id  AND t1.ybhnumber = t3.create_by   GROUP BY  taskid) t6 on t1.id=(t6.taskid)  " +
                     "left join (select count(fwbh) count,taskid,count(distinct(villagedm)) villagecount,sum(case when hyzt='1' then 1 else 0 end ) hycount,sum(case when hyzt is null  then 1 else 0 end ) whycount from  zm_taskdetail group by taskid) t5 on t1.id=t5.taskid";

                sql_count += " and taskname like '%" + txtKeyWord + "%'" + strWhere;
            }
            else if (string.IsNullOrEmpty(txtKeyWord) && !string.IsNullOrEmpty(pch))
            {
                sql = "select ROW_NUMBER() OVER()index,t1.*,t2.region_name xzq,t3.region_name town,t4.region_name village,t5.count cqcount1,t5.villagecount,COALESCE(t5.hycount,0) hycount,COALESCE(t5.whycount,0) whycount,CONCAT(t2.region_name, t3.region_name, t4.region_name) AS full_region,TO_CHAR(t1.create_on, 'YYYY-MM-DD HH24:MI:SS') time,t6.ydb,t6.wdb,concat(\r\n                \r\n\t\t\t\t\t\t\t\t  CASE WHEN t6.ydb = 0   THEN 0.00\r\n\t\t\t\t\t\t\t\t\tWHEN t5.hycount = 0   THEN 0.00\r\n                  WHEN round(t6.ydb::numeric / t5.hycount::numeric * 100, 2) > 100 THEN 100.00\r\n                  ELSE round(t6.ydb::numeric / t5.hycount::numeric * 100, 2)\r\n                END, '%') AS dbl,\tconcat (\r\n\tCASE\r\n\t\t\t\r\n\t\t\tWHEN t6.wdb = 0 THEN\r\n\t\t\t0.00 \r\n\t\t\tWHEN t5.hycount = 0 THEN\r\n\t\t\t0.00 \r\n\t\t\tWHEN round( t6.wdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) > 100 THEN\r\n\t\t\t100.00 ELSE round( t6.wdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) \r\n\t\tEND,\r\n\t\t'%' \r\n\t) AS wdbl,t6.qtwdb,concat (CASE WHEN t6.qtwdb = 0 THEN 0.00 WHEN t5.hycount = 0 THEN 0.00  WHEN round( t6.qtwdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) > 100 THEN 100.00 ELSE round( t6.qtwdb :: NUMERIC / t5.hycount :: NUMERIC * 100, 2 ) END,'%' ) AS qtwdbl\t  from " +
                    "(select * from  zm_task   t where year='"+ year + "'   and pch='"+pch+ "'"+strWhere+"   order by create_on  desc   limit " + pageSize + " offset (" + pageIndex + " - 1) * " + pageSize + ") t1 " +
                    "left join zm_department t2 on t1.xzqdm=(t2.id) " +
                     "left join zm_department t3 on concat(t1.towndm,'000')=(t3.id)" +
                    "left join zm_department t4 on concat(t1.villagedm)=(t4.id) " +
                     "left join (select t1.taskid,COALESCE(sum(case when t2.jsrysstd='是' then 1 else 0 END),0) ydb,COALESCE(sum(case when t2.jsrysstd='否' then 1 else 0 END),0) wdb,COALESCE ( SUM ( CASE WHEN t2.wdwhjgfjgqsyyrkrgbdj = 1    or t3.sfczckfhwggptzl = '1'   or t3.use_electric_bicycle=1 \r\n   or t3.use_bottled_liquefied_gas=1  or t3.use_stacked_items ='1'  THEN 1 ELSE 0 END ), 0 ) qtwdb  from zm_taskdetail  t1 left join   zm_pc_baselog_sqjz t2 ON t1.fwbh = t2.fwbh  and t1.baseid=t2.id and t1.ybhnumber=t2.create_by  LEFT JOIN zm_pc_yhlog_sqjz t3 ON t1.fwbh = t3.fwbh  AND t1.baseid = t3.base_id  AND t1.ybhnumber = t3.create_by GROUP BY  taskid) t6 on t1.id=(t6.taskid)  " +
                     "left join (select count(fwbh) count,taskid,count(distinct(villagedm)) villagecount,sum(case when hyzt='1' then 1 else 0 end ) hycount,sum(case when hyzt is null  then 1 else 0 end ) whycount from  zm_taskdetail group by taskid) t5 on t1.id=t5.taskid";

                sql_count += " and  pch='" + pch + "'" + strWhere;
            }



            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 条件房屋数量
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getCqInfo")]
        public RespRet<Page<object>> getCqInfo([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string cs,[FromQuery] string pch)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
            string year = DateTime.Now.Year.ToString();


            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            DateTime endData = Convert.ToDateTime(today);
            table_fwzt = "zm_building_fwzt_" + endData.Year;

            //某条件下的总数量  及 剩余数量
            string sql_count = "select count(fwbh) from zm_buildings   WHERE fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' ))";

            string sql_count2= "select count(fwbh) from zm_buildings   WHERE fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and cs>=2";


            string sql = "and fwbh not in (SELECT fwbh FROM zm_taskdetail where year='"+year+"')";


            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                sql_count += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
                sql_count2+= " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                sql_count += " and xzqdm='" + xzqdm + "' and town='" + towndm + "'";
                sql_count2 += " and xzqdm='" + xzqdm + "' and town='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                sql_count += " and xzqdm='" + xzqdm + "' ";
                sql_count2 += " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(cs) && (cs == "1,2"))  //1,2
            {
                sql_count += " and  (cs=1 or cs=2 or cs is null or cs=0) ";
            }
            else  if (!string.IsNullOrEmpty(cs) && (cs == "2"))// 2
            {
                sql_count += " and  cs in (" + cs + ") ";
            }
            else  if (!string.IsNullOrEmpty(cs) && ( cs == "1")) //1
            {
                sql_count += " and  (cs=1 or cs is null or cs=0) ";
            }
            else if (!string.IsNullOrEmpty(cs) && cs == "1,3") //1,3 
            {
                sql_count += " and  (cs=1 or cs>=3 or cs is null or cs=0) ";
            }
            else if (!string.IsNullOrEmpty(cs) && cs == "2,3")
            {
                sql_count += " and  (cs=2 or cs>=3) ";
            }
            else if (!string.IsNullOrEmpty(cs) && cs == "3")
            {
                sql_count += " and  (cs>=3) ";
            }
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count); //原始房屋数量
            int count10 = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count2); //原始房屋数量 2层以上数量



            int count2 = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count+ sql); //剩余房屋数量 分配了一部分后
            int count20 = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count2 + sql); //剩余房屋 2层以上数量
            List<object> item = new List<object>();
            item.Add(count10);
            item.Add(count20);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = count,PageTotal = count2, Items = item });

        }


        /// <summary>
        /// 用户查询 按照级别查询
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getRenYuanlist")]
        public RespRet<Page<object>> getRenYuanlist([FromQuery] string role_type)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string sql = "select nickname,number from zm_user_cj where role_type='" + role_type + "' AND nickname!=''";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

            int count = list.Count; //总数量

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, PageTotal = count, Total = count });

        }



        /// <summary>
        /// 用户查询 按照级别查询
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getXZList")]
        public RespRet<Page<object>> getXZList([FromQuery] string pch)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string year = DateTime.Now.Year.ToString();


            string sql = @"select ROW_NUMBER() OVER ()index,t4.xzq,t1.xzqdm,COALESCE(t1.zcount,0) zcount,COALESCE(t1.yhycount,0) yhycount,COALESCE(t2.bhgcount,0) bhgcount,COALESCE(t3.xzcount,0) xzcount,COALESCE(t2.bhgcount-t3.xzcount,0)   sycount  from (select xzqdm,count(1) zcount,sum(case when hyzt=1 then 1 else 0 end) yhycount  from zm_taskdetail where pch= '"+pch+"'  and year = '"+ year + "' GROUP BY xzqdm) t1"+
            " left join (select xzqdm,xzq,sum(case when jsrysstd='否' then 1 else 0 end) bhgcount from zm_pc_baselog_sqjz where id in (select baseid from zm_taskdetail where pch='"+pch+"'  and year='"+year+"' )   GROUP BY xzqdm,xzq) t2  on t1.xzqdm=t2.xzqdm"+
             " left join (select xzqdm, xzq, sum(case when jsrysstd= '是' then 1 else 0 end) xzcount from(select DISTINCT ON (t.fwbh) t.fwbh, t.xzqdm, t.xzq, t.yhly, t.id, t.create_on, t.jsrysstd, t.create_name, t.cs from zm_pc_baselog_sqjz t  where fwbh in (select fwbh from zm_pc_baselog_sqjz where id in (select baseid from zm_taskdetail where pch= '"+pch+"'  and year = '"+year+"')  and jsrysstd = '否')   and yhly = 2   ORDER BY t.fwbh, t.create_on DESC) tt GROUP BY xzqdm, xzq) t3 on t1.xzqdm = t3.xzqdm"+
             " left join zm_dic_xzq t4 on t1.xzqdm=t4.xzqdm";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

            int count = list.Count; //总数量

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, PageTotal = count, Total = count });

        }



        /// <summary>
        /// 用户查询 按照级别查询 按组分配
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/saveTaskold")]
        public RespRet<Page<object>> saveTaskold([FromQuery] string taskname, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string cs, [FromQuery] string cqcount, [FromQuery] string cqbl, [FromQuery] string fwcount, [FromQuery] string sycount, [FromQuery] string pch, [FromQuery] string zname)
        {
            try
            {
                var userNumber = this.CurrentUser();
                if (string.IsNullOrEmpty(userNumber))
                {
                    return new RespRet<Page<object>>(false, "user not logined");
                }

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                if (null == user)
                {
                    return new RespRet<Page<object>>(false, "用户名不存在");
                }


                string uuid = Guid.NewGuid().ToString("N");
                #region 按照指定的进行分配 分配到多少人
                var quotedValues = zname.Split(",").Select(v => $"'{v.Replace("'", "''")}'");
                string inClause = string.Join(", ", quotedValues);

                string sql = "SELECT  zname,STRING_AGG(CONCAT(zname, '-', nickname), ',') AS nickname, STRING_AGG(number::text, ',') AS number FROM    zm_task_zuser  where zname  in (" + inClause + ")  GROUP BY    zname ORDER BY      zname ";
                var list1 = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                if (list1.Count == 0)
                {
                    return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = -1 });
                }
                string numbers = "";
                string nicknames = "";
                List<zinfo> zinfos = new List<zinfo>();
                foreach (object obj in list1)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {
                        zinfo zmodel = new zinfo();
                        var properties = obj as IDictionary<string, object>;
                        foreach (var kvp in properties)
                        {
                            if (kvp.Key == "nickname")
                            {
                                zmodel.nickname = kvp.Value.ToString();
                                nicknames += zmodel.nickname + ",";
                            }
                            else if (kvp.Key == "number")
                            {
                                zmodel.number = kvp.Value.ToString();
                                numbers += kvp.Value + ",";
                            }
                            else if (kvp.Key == "zname")
                            {
                                zmodel.zname = kvp.Value.ToString();
                            }
                        }
                        zinfos.Add(zmodel);
                    }
                }
                string[] arrs = numbers.TrimEnd(',').Split(",");
                if (int.Parse(cqcount) < arrs.Length)
                {
                    return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = -2 });
                }
                #endregion


                string year = DateTime.Now.Year.ToString();
                string sqlxzq = "";

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;


                #region 选择区域 涉及多少村 每个村目前现状多少 
                string sqlcun = @"SELECT * from (SELECT t1.*,COALESCE(t2.twosycount,0) twosycount,COALESCE(t2.onesycount,0) onesycount  FROM (
                                  SELECT xzqdm, towndm,villagedm, COUNT ( fwbh ) zccount,SUM(CASE WHEN cs >= 2 THEN 1 ELSE 0 END) twocount,SUM(CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END) onecount FROM zm_buildings
                                  WHERE fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' ))  GROUP BY xzqdm, towndm, villagedm ) t1 LEFT JOIN( SELECT xzqdm, towndm, villagedm, SUM(CASE WHEN cs >= 2 THEN 1 ELSE 0 END) twosycount, SUM(CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END) onesycount FROM zm_buildings  WHERE  fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "')  GROUP BY xzqdm, towndm, villagedm) t2 ON t1.villagedm = t2.villagedm)  t1 where 1=1 ";


                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
                {
                    sqlcun += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "000' and villagedm='" + villagedm + "'";
                }
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    sqlcun += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "000'";
                }
                if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    sqlcun += " and xzqdm='" + xzqdm + "' ";
                }

                var cunlist = _dbContext.Database.GetDbConnection().Query<object>(sqlcun + "order by twosycount").ToList();
                int cuncount = cunlist.Count;
                List<cuninfo> cunifos = new List<cuninfo>();
                foreach (object obj in cunlist)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    cuninfo model = new cuninfo();
                    if (obj != null)
                    {
                        var properties = obj as IDictionary<string, object>;
                        foreach (var kvp in properties)
                        {
                            if (kvp.Key == "xzqdm")
                            {
                                model.xzqdm = kvp.Value.ToString();
                            }
                            else if (kvp.Key == "towndm")
                            {
                                model.towndm = kvp.Value.ToString().Replace("000", "");
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                model.villagedm = kvp.Value.ToString();
                            }
                            else if (kvp.Key == "zcount")
                            {
                                model.zcount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "twocount")
                            {
                                model.twocount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "onecount")
                            {
                                model.onecount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "zccount")
                            {
                                model.zccount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "onesycount")
                            {
                                model.onesycount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "twosycount")
                            {
                                model.twosycount = Convert.ToInt32(kvp.Value.ToString());
                            }
                        }
                        cunifos.Add(model);
                    }
                }
                #endregion
                int countfw = 0;
                #region 找房子
                //先处理zcount小于10栋的 且2层以上有剩余 或者 1层有剩余的，只取一栋
                for (int i = 0; i < cunifos.Count; i++)
                {
                    cuninfo model = cunifos[i];
                    double value = 0;
                    if (model.zccount <= 10 && (model.onesycount > 0 || model.twosycount > 0))
                    {
                        value = model.zccount;
                        sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))  fwbh  from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "')  and villagedm='" + model.villagedm + "'  order by COALESCE(NULLIF(cs, 0), 1)  desc  limit 1";
                        var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                        model.fwbhs.AddRange(list2);
                        countfw += model.fwbhs.Count;
                        model.iscq = true;
                    }
                }
                //再处理10栋以上的  已抽取countfw 再抽取
                int sycqcount = int.Parse(cqcount) - countfw; //剩余需要抽取的
                int syfwcount = int.Parse(fwcount) - countfw;//剩余总量
                //string cqbl2 = syfwcount == 0? "0%": $"{(double)sycqcount / syfwcount * 100:F2}%";

                string cqbl2 = cqbl;
                for (int i = 0; i < cunifos.Count; i++)
                {
                    cuninfo model = cunifos[i]; //抽取百分之三十
                    double value = 0;
                    if (!model.iscq)
                    {
                        if (i == cunifos.Count - 1)
                        {
                            value = int.Parse(cqcount) - countfw;
                            if (value < 0)
                            {
                                value = 0;
                            }
                        }
                        else
                        {
                            if (string.IsNullOrEmpty(cqbl2) || !cqbl2.EndsWith("%"))
                                throw new ArgumentException("百分比格式无效，应为 'XX%'。");

                            if (!double.TryParse(cqbl2.TrimEnd('%'), out double percent) || percent < 0)
                                throw new ArgumentException("百分比数值无效。");

                            double ratio = percent / 100.0;

                            if (model.zccount == 1309)
                            {

                            }
                            // 计算抽样数量（向下取整）
                            double sampled = model.zccount * ratio;
                            int result = (int)Math.Round(sampled);
                            value = Math.Min(result, model.zccount); //抽取的数量
                        }
                        model.fwbhs.Clear();
                        if (value <= model.twosycount)  //抽取数量 小于2层及2层以上
                        {
                            sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))   fwbh from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "') and cs>=2 and villagedm='" + model.villagedm + "'   limit " + value;
                            var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                            model.fwbhs.AddRange(list2);
                        }
                        else if (value > model.twosycount) //抽取数量大于 2层及2层以上
                        {
                            double count2 = value - model.twosycount;
                            if (model.twosycount > 0)
                            {
                                //先拿走2层及以上的
                                sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))  fwbh  from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "') and cs>=2 and villagedm='" + model.villagedm + "'";
                                var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                                model.fwbhs.AddRange(list2);
                            }

                            if (count2 <= model.onesycount) //数量 小于 1层 剩余数量
                            {
                                sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))   fwbh from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "') and (cs=1 or cs=0 or cs is null)  and villagedm='" + model.villagedm + "'   limit " + count2;
                                var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                                model.fwbhs.AddRange(list2);
                            }
                            else
                            {
                                sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))  fwbh  from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "') and (cs=1 or cs=0 or cs is null)  and villagedm='" + model.villagedm + "'   limit " + count2;
                                var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                                model.fwbhs.AddRange(list2);
                            }
                        }

                        countfw += model.fwbhs.Count;
                    }
                }
                #endregion
                //获取的数量多了 从最后一个村 
                if (countfw > int.Parse(cqcount))
                {
                    int cha = countfw - int.Parse(cqcount);

                    cunifos.Sort((a, b) => b.fwbhs.Count.CompareTo(a.fwbhs.Count));

                    cuninfo model = cunifos[0];
                    if (model.fwbhs.Count >= cha)
                    {
                        countfw = cunifos.Sum(c => c.fwbhs.Count);
                        model.fwbhs.RemoveRange(model.fwbhs.Count - cha, cha); // 移除最后几个要素
                    }
                    countfw = cunifos.Sum(c => c.fwbhs.Count);
                }

                #region 按组平均分分配 尽量保证每组 房屋数量和村数量一致
                int totalHouses = cunifos.Sum(v => v.fwbhs.Count);
                int avgHousesPerGroup = totalHouses / zinfos.Count;
                var villages = cunifos.OrderByDescending(v => v.fwbhs.Count).ToList();

                var groupHouseCounts = zinfos.ToDictionary(g => g, g => 0);
                for (int i = 0; i < villages.Count; i++)
                {
                    var village = villages[i];
                    bool assigned = false;

                    // 尝试找到最适合的组
                    var bestGroup = zinfos
                        .Where(g => groupHouseCounts[g] + village.fwbhs.Count <= avgHousesPerGroup)
                        .OrderBy(g => groupHouseCounts[g])
                        .FirstOrDefault();

                    if (bestGroup != null)
                    {
                        bestGroup.assignedFwbhs.AddRange(village.fwbhs);
                        groupHouseCounts[bestGroup] += village.fwbhs.Count;
                        assigned = true;
                    }

                    if (!assigned)
                    {
                        var minGroup = zinfos.OrderBy(g => groupHouseCounts[g]).First();
                        int maxCanAssign = (int)(avgHousesPerGroup) - groupHouseCounts[minGroup];
                        int toAssign = Math.Min(maxCanAssign, village.fwbhs.Count);

                        if (toAssign > 0)
                        {
                            minGroup.assignedFwbhs.AddRange(village.fwbhs.Take(toAssign));
                            groupHouseCounts[minGroup] += toAssign;
                            village.fwbhs.RemoveRange(0, toAssign);

                            if (village.fwbhs.Count > 0)
                            {
                                villages.Add(village);
                                // 不需要重新排序，因为我们使用索引访问
                            }
                        }
                        else
                        {
                            minGroup.assignedFwbhs.AddRange(village.fwbhs);
                            groupHouseCounts[minGroup] += village.fwbhs.Count;
                        }
                    }
                }
                #endregion

                #region 组分配到人 
                for (int i = 0; i < zinfos.Count; i++)
                {
                    zinfo model = zinfos[i];
                    int zcount = model.assignedFwbhs.Count;//该组分配房屋数量

                    string[] arr = model.number.Split(",");
                    int rycount = arr.Length;//该组分配人数

                    // 计算每人应分到的房屋数
                    int baseHouses = zcount / rycount;
                    int remainder = zcount % rycount;
                    int currentIndex = 0;
                    for (int j = 0; j < rycount; j++)
                    {
                        int ryassignedCount = baseHouses + (j < remainder ? 1 : 0);
                        var ryassignedFwbhs = model.assignedFwbhs
                            .Skip(currentIndex)
                            .Take(ryassignedCount)
                        .ToList();

                        string ry = arr[j].ToString();

                        for (int k = 0; k < ryassignedFwbhs.Count; k++)
                        {
                            if (ryassignedFwbhs[k].Split(",").Length == 5)
                            {
                                string fpfwbh = ryassignedFwbhs[k].Split(",")[0].ToString();
                                string xzqdm1 = ryassignedFwbhs[k].Split(",")[1].ToString();
                                string towndm1 = ryassignedFwbhs[k].Split(",")[2].ToString().EndsWith("000") ? ryassignedFwbhs[k].Split(",")[2].ToString().Substring(0, ryassignedFwbhs[k].Split(",")[2].ToString().Length - 3) : ryassignedFwbhs[k].Split(",")[2].ToString();
                                string villagedm1 = ryassignedFwbhs[k].Split(",")[3].ToString();
                                string cs1 = ryassignedFwbhs[k].Split(",")[4].ToString();

                                string sql2 = " INSERT INTO public.zm_taskdetail " +
                                        "(id,  taskid, xzqdm, towndm, villagedm, fwbh, create_on, number,pch,year,cs) VALUES" +
                                        " ('" + Guid.NewGuid().ToString("N") + "', '" + uuid + "', '" + xzqdm1 + "', '" + towndm1 + "', '" + villagedm1 + "', '" + fpfwbh + "','" + DateTime.Now + "', '" + ry + "','" + pch + "','" + year + "'," + cs1 + ");";
                                int mn = _dbContext.Database.ExecuteSqlRaw(sql2);
                            }
                        }
                        currentIndex += ryassignedCount;
                    }
                }
                #endregion 

                string jlcs = cs;
                if (string.IsNullOrEmpty(cs) || cs == "1,2,3")
                {
                    jlcs = "1,2,3";
                }

                #region 添加任务
                sql = " INSERT INTO public.zm_task " +
                              "(id, taskname, cs, cqbl, cqcount, taskrys, create_on, create_user, xzqdm, towndm, villagedm, status,zcount,sycount,pch,zname,taskrysname,year) VALUES" +
                              " ('" + uuid + "', '" + taskname + "', '" + jlcs + "', '" + cqbl + "', '" + cqcount + "', '" + numbers + "', '" + DateTime.Now + "', '" + user.Nickname + "', '" + xzqdm + "', '" + towndm + "', '" + villagedm + "',  '','" + fwcount + "','" + sycount + "','" + pch + "','" + zname + "','" + nicknames + "','" + year + "');";


                var num = _dbContext.Database.ExecuteSqlRaw(sql);
                #endregion

                return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = countfw });
            }
            catch (Exception ex)
            {
                return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = 0 });
            }

        }


        /// <summary>
        /// 用户查询 按照级别查询 按人员分配 参数zname 为number的集合
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/saveTask")]
        public RespRet<Page<object>> saveTask([FromQuery] string taskname, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string cs, [FromQuery] string cqcount, [FromQuery] string cqbl,[FromQuery] string fwcount, [FromQuery] string sycount,[FromQuery] string pch, [FromQuery] string zname)
        {
            try
            {
                var userNumber = this.CurrentUser();
                if (string.IsNullOrEmpty(userNumber))
                {
                    return new RespRet<Page<object>>(false, "user not logined");
                }

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                if (null == user)
                {
                    return new RespRet<Page<object>>(false, "用户名不存在");
                }


                string uuid = Guid.NewGuid().ToString("N");
                #region 按照指定的进行分配 分配到多少人
                var quotedValues = zname.Split(",").Select(v => $"'{v.Replace("'", "''")}'");
                string inClause = string.Join(", ", quotedValues);

                string sql = "SELECT  *  FROM    zm_task_zuser  where number  in (" + inClause + ") ";
                var list1 = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                if (list1.Count == 0)
                {
                    return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = -1 });
                }
                string numbers = "";
                string ybhnumbers = "";
                string nicknames = "";
                List<zinfo> zinfos = new List<zinfo>();
                foreach (object obj in list1)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {
                        zinfo zmodel = new zinfo();
                        var properties = obj as IDictionary<string, object>;
                        foreach (var kvp in properties)
                        {
                            if (kvp.Key == "nickname")
                            {
                                zmodel.nickname = kvp.Value.ToString();
                                nicknames+= zmodel.nickname + ",";
                            }
                            else if (kvp.Key == "number")//用户的qaakey
                            {
                                zmodel.number = kvp.Value.ToString();
                                numbers += kvp.Value + ",";
                            }
                            else if (kvp.Key == "ybhnumber")//用户的number
                            {
                                zmodel.ybhnumber = kvp.Value.ToString();
                                ybhnumbers += kvp.Value + ",";  
                            }
                            else if (kvp.Key == "zname")
                            {
                                zmodel.zname = kvp.Value.ToString();
                            }
                        }
                        zinfos.Add(zmodel);
                    }
                }
                string[] arrs = numbers.TrimEnd(',').Split(",");
                if (int.Parse(cqcount) < arrs.Length)
                {
                    return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = -2 });
                }
                #endregion

                
                string year = DateTime.Now.Year.ToString();
                string sqlxzq = "";

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;


                #region 选择区域 涉及多少村 每个村目前现状多少 
                string sqlcun = @"SELECT * from (SELECT t1.*,COALESCE(t2.twosycount,0) twosycount,COALESCE(t2.onesycount,0) onesycount  FROM (
                                  SELECT xzqdm, towndm,villagedm, COUNT ( fwbh ) zccount,SUM(CASE WHEN cs >= 2 THEN 1 ELSE 0 END) twocount,SUM(CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END) onecount FROM zm_buildings
                                  WHERE fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '"+ today + " 23:59:59' ))  GROUP BY xzqdm, towndm, villagedm ) t1 LEFT JOIN( SELECT xzqdm, towndm, villagedm, SUM(CASE WHEN cs >= 2 THEN 1 ELSE 0 END) twosycount, SUM(CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END) onesycount FROM zm_buildings  WHERE  fwbh not in (select distinct fwbh from " + table_fwzt+" where  (final_fwzt IS NOT NULL) and (final_create_on = '"+ today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year+"')  GROUP BY xzqdm, towndm, villagedm) t2 ON t1.villagedm = t2.villagedm)  t1 where 1=1 ";

                
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
                {
                    sqlcun+= " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "000' and villagedm='" + villagedm + "'";
                }
                if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    sqlcun+= " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "000'";
                }
                if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    sqlcun += " and xzqdm='" + xzqdm + "' ";
                }

                var cunlist = _dbContext.Database.GetDbConnection().Query<object>(sqlcun+ "order by twosycount").ToList();
                int cuncount = cunlist.Count;
                List<cuninfo> cunifos = new List<cuninfo>();
                foreach (object obj in cunlist)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    cuninfo model = new cuninfo();
                    if (obj != null)
                    {
                        var properties = obj as IDictionary<string, object>;
                        foreach (var kvp in properties)
                        {
                            if (kvp.Key == "xzqdm")
                            {
                                model.xzqdm= kvp.Value.ToString();
                            }
                            else if (kvp.Key == "towndm")
                            {
                                model.towndm = kvp.Value.ToString().Replace("000", "");
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                model.villagedm = kvp.Value.ToString();
                            }
                            else if (kvp.Key == "zcount")
                            {
                                model.zcount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "twocount")
                            {
                                model.twocount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "onecount")
                            {
                                model.onecount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "zccount")
                            {
                                model.zccount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "onesycount")
                            {
                                model.onesycount = Convert.ToInt32(kvp.Value.ToString());
                            }
                            else if (kvp.Key == "twosycount")
                            {
                                model.twosycount = Convert.ToInt32(kvp.Value.ToString());
                            }
                        }
                        cunifos.Add(model);
                    }
                }
                #endregion
                int countfw = 0;
                #region 找房子
                //先处理zcount小于10栋的 且2层以上有剩余 或者 1层有剩余的，只取一栋
                for (int i = 0; i < cunifos.Count; i++)
                {
                    cuninfo model = cunifos[i];
                    double value = 0;
                    if (model.zccount <= 10 && (model.onesycount>0 ||  model.twosycount>0))
                    {
                        value = model.zccount;
                        sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))  fwbh  from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "')  and villagedm='" + model.villagedm + "'  order by COALESCE(NULLIF(cs, 0), 1)  desc  limit 1";
                        var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                        model.fwbhs.AddRange(list2);
                        countfw += model.fwbhs.Count;
                        model.iscq = true;
                    }
                }
                //再处理10栋以上的  已抽取countfw 再抽取
                int sycqcount = int.Parse(cqcount) - countfw; //剩余需要抽取的
                int syfwcount= int.Parse(fwcount)- countfw;//剩余总量
                //string cqbl2 = syfwcount == 0? "0%": $"{(double)sycqcount / syfwcount * 100:F2}%";

                string cqbl2 = cqbl;
                for (int i = 0; i < cunifos.Count; i++)
                {
                    cuninfo model = cunifos[i]; //抽取百分之三十
                    double value = 0;
                    if (!model.iscq)
                    {
                        //if (i == cunifos.Count - 1)
                        //{
                        //    //value = int.Parse(cqcount) - countfw;
                        //    //if (value < 0)
                        //    //{
                        //    //    value = 0;
                        //    //}
                        //}
                        //else
                        {
                            if (string.IsNullOrEmpty(cqbl2) || !cqbl2.EndsWith("%"))
                                throw new ArgumentException("百分比格式无效，应为 'XX%'。");

                            if (!double.TryParse(cqbl2.TrimEnd('%'), out double percent) || percent < 0)
                                throw new ArgumentException("百分比数值无效。");

                            double ratio = percent / 100.0;

                            if (model.zccount == 1138)
                            {

                            }
                            // 计算抽样数量（向下取整）
                            double sampled = model.zccount * ratio;
                            int result = (int)Math.Round(sampled, MidpointRounding.AwayFromZero);
                            value = Math.Min(result, model.zccount); //抽取的数量
                        }
                        model.fwbhs.Clear();
                        if (value <= model.twosycount)  //抽取数量 小于2层及2层以上
                        {
                            sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))   fwbh from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "') and cs>=2 and villagedm='" + model.villagedm + "'   limit " + value;
                            var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                            model.fwbhs.AddRange(list2);
                        }
                        else if (value > model.twosycount) //抽取数量大于 2层及2层以上
                        {
                            double count2 = value - model.twosycount;
                            if (model.twosycount > 0)
                            {
                                //先拿走2层及以上的
                                sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))  fwbh  from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "') and cs>=2 and villagedm='" + model.villagedm + "'";
                                var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                                model.fwbhs.AddRange(list2);
                            }

                            if (count2 <= model.onesycount) //数量 小于 1层 剩余数量
                            {
                                sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))   fwbh from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "') and (cs=1 or cs=0 or cs is null)  and villagedm='" + model.villagedm + "'   limit " + count2;
                                var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                                model.fwbhs.AddRange(list2);
                            }
                            else
                            {
                                sql = "select CONCAT(fwbh,',',xzqdm, ',', towndm,',',villagedm,',',COALESCE(NULLIF(cs, 0), 1))  fwbh  from zm_buildings where fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on = '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + year + "') and (cs=1 or cs=0 or cs is null)  and villagedm='" + model.villagedm + "'   limit " + count2;
                                var list2 = _dbContext.Database.GetDbConnection().Query<string>(sql, new { villagedm = model.villagedm }).ToList();
                                model.fwbhs.AddRange(list2);
                            }
                        }
                        
                        countfw += model.fwbhs.Count;
                    }
                }
                #endregion
                //获取的数量多了 从最后一个村 
                if (countfw > int.Parse(cqcount))
                {
                    //int cha = countfw - int.Parse(cqcount);
                    
                    //cunifos.Sort((a, b) => b.fwbhs.Count.CompareTo(a.fwbhs.Count));

                    //cuninfo model = cunifos[0];
                    //if (model.fwbhs.Count >= cha)
                    //{
                    //    countfw = cunifos.Sum(c => c.fwbhs.Count);
                    //    model.fwbhs.RemoveRange(model.fwbhs.Count - cha, cha); // 移除最后几个要素
                    //}
                    //countfw = cunifos.Sum(c => c.fwbhs.Count);
                }

                #region 按组平均分分配 尽量保证每组 房屋数量和村数量一致
                int totalHouses = cunifos.Sum(v => v.fwbhs.Count);
                int avgHousesPerGroup = totalHouses / zinfos.Count;
                var villages = cunifos.OrderByDescending(v => v.fwbhs.Count).ToList();

                var groupHouseCounts = zinfos.ToDictionary(g => g, g => 0);
                for (int i = 0; i < villages.Count; i++)
                {
                    var village = villages[i];
                    bool assigned = false;

                    // 尝试找到最适合的组
                    var bestGroup = zinfos
                        .Where(g => groupHouseCounts[g] + village.fwbhs.Count <= avgHousesPerGroup)
                        .OrderBy(g => groupHouseCounts[g])
                        .FirstOrDefault();

                    if (bestGroup != null)
                    {
                        bestGroup.assignedFwbhs.AddRange(village.fwbhs);
                        groupHouseCounts[bestGroup] += village.fwbhs.Count;
                        assigned = true;
                    }

                    if (!assigned)
                    {
                        var minGroup = zinfos.OrderBy(g => groupHouseCounts[g]).First();
                        int maxCanAssign = (int)(avgHousesPerGroup) - groupHouseCounts[minGroup];
                        int toAssign = Math.Min(maxCanAssign, village.fwbhs.Count);

                        if (toAssign > 0)
                        {
                            minGroup.assignedFwbhs.AddRange(village.fwbhs.Take(toAssign));
                            groupHouseCounts[minGroup] += toAssign;
                            village.fwbhs.RemoveRange(0, toAssign);

                            if (village.fwbhs.Count > 0)
                            {
                                villages.Add(village);
                                // 不需要重新排序，因为我们使用索引访问
                            }
                        }
                        else
                        {
                            minGroup.assignedFwbhs.AddRange(village.fwbhs);
                            groupHouseCounts[minGroup] += village.fwbhs.Count;
                        }
                    }
                }
                #endregion

                #region 组分配到人 
                for (int i = 0; i < zinfos.Count; i++)
                {
                    zinfo model = zinfos[i];
                    int zcount = model.assignedFwbhs.Count;//该组分配房屋数量

                    string[] arr = model.number.Split(",");
                    int rycount = arr.Length;//该组分配人数

                    // 计算每人应分到的房屋数
                    int baseHouses = zcount / rycount;
                    int remainder = zcount % rycount;
                    int currentIndex = 0;
                    for (int j = 0; j < rycount; j++)
                    {
                        int ryassignedCount = baseHouses + (j < remainder ? 1 : 0);
                        var ryassignedFwbhs = model.assignedFwbhs
                            .Skip(currentIndex)
                            .Take(ryassignedCount)
                        .ToList();

                        string ry = arr[j].ToString();

                        for (int k = 0; k < ryassignedFwbhs.Count; k++)
                        {
                            if (ryassignedFwbhs[k].Split(",").Length == 5)
                            {
                                string fpfwbh = ryassignedFwbhs[k].Split(",")[0].ToString();
                                string xzqdm1 = ryassignedFwbhs[k].Split(",")[1].ToString();
                                string towndm1 = ryassignedFwbhs[k].Split(",")[2].ToString().EndsWith("000") ? ryassignedFwbhs[k].Split(",")[2].ToString().Substring(0, ryassignedFwbhs[k].Split(",")[2].ToString().Length - 3) : ryassignedFwbhs[k].Split(",")[2].ToString();
                                string villagedm1 = ryassignedFwbhs[k].Split(",")[3].ToString();
                                string cs1 = ryassignedFwbhs[k].Split(",")[4].ToString();

                                string sql2 = " INSERT INTO public.zm_taskdetail " +
                                        "(id,  taskid, xzqdm, towndm, villagedm, fwbh, create_on,ybhnumber, number,pch,year,cs) VALUES" +
                                        " ('" + Guid.NewGuid().ToString("N") + "', '" + uuid + "', '" + xzqdm1 + "', '" + towndm1 + "', '" + villagedm1 + "', '" + fpfwbh + "','" + DateTime.Now + "','"+ model.ybhnumber+"','" + ry + "','" + pch + "','" + year + "'," + cs1 + ");";
                                int mn = _dbContext.Database.ExecuteSqlRaw(sql2);
                            }
                        }
                        currentIndex += ryassignedCount;
                    }
                }
                #endregion 

                string jlcs = cs;
                if (string.IsNullOrEmpty(cs) || cs == "1,2,3")
                {
                    jlcs = "1,2,3";
                }

                #region 添加任务
                sql = " INSERT INTO public.zm_task " +
                              "(id, taskname, cs, cqbl, cqcount, taskrys, create_on, create_user, xzqdm, towndm, villagedm, status,zcount,sycount,pch,zname,taskrysname,year) VALUES" +
                              " ('" + uuid + "', '" + taskname + "', '" + jlcs + "', '" + cqbl + "', '" + cqcount + "', '" + numbers + "', '" + DateTime.Now + "', '" + user.Nickname + "', '" + xzqdm + "', '" + towndm + "', '" + villagedm + "',  '','" + fwcount + "','" + sycount + "','" + pch + "','','" + nicknames + "','" + year + "');";


                var num = _dbContext.Database.ExecuteSqlRaw(sql);
                #endregion

                return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = countfw });
            }
            catch (Exception ex)
            {
                return new RespRet<Page<object>>(true, "ok", new Page<object> { Total = 0 });
            }

        }
        public static Dictionary<int, List<string>> DistributeEvenly(List<string> source, int groupCount)
        {
            var distribution = new Dictionary<int, List<string>>();

            // 初始化字典中的列表
            for (int i = 0; i < groupCount; i++)
            {
                distribution[i] = new List<string>();
            }

            // 计算每组的基本大小和余数
            int baseSize = source.Count / groupCount;
            int remainder = source.Count % groupCount;

            int currentIndex = 0;

            // 分配元素
            for (int group = 0; group < groupCount; group++)
            {
                // 计算当前组的大小（前remainder组会多一个元素）
                int currentGroupSize = baseSize + (group < remainder ? 1 : 0);

                // 添加元素到当前组
                distribution[group].AddRange(source.GetRange(currentIndex, currentGroupSize));
                currentIndex += currentGroupSize;
            }

            return distribution;
        }


        /// <summary>
        /// 根据任务ID获取核验情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getTaskHouseList")]
        public RespRet<Page<object>> getTaskHouseList([FromQuery] string taskid, [FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string hyzt, [FromQuery] string txtKeyWord, [FromQuery] string nickname, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string villagedm, [FromQuery] string jsrysstd)
        {
           var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (!string.IsNullOrEmpty(hyzt)) 
            {
                if (hyzt == "已核验")
                {
                    strWhere += " and hyzt='1'";
                }
                else 
                {
                    strWhere += " and hyzt is null";
                }
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }
            if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
            {
                strWhere += " and (yh_create_on  between '" + startTime + "' and '" + endTime + " 23:59:59')";
            }
            if (!string.IsNullOrEmpty(nickname) )
            {
                strWhere += " and (nickname  like '%" + nickname+"%')";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and (villagedm  ='" + villagedm + "')";
            }
            if (!string.IsNullOrEmpty(jsrysstd))
            {
                strWhere += " and (jsrysstd  ='" + jsrysstd + "')";
            }
            string sql = @"SELECT ROW_NUMBER( ) OVER ( ) INDEX,* FROM (
	SELECT T.*,M.create_on yh_create_on,M.create_name yh_create_name,M.jsrysstd FROM (SELECT t1.fwbh,
			t1.taskid,
			t1.hyzt,
			t1.baseid,
			t1.ybhnumber,nickname,hzxm,address,t3.xzq,t3.town,t3.village,t3.villagedm,t1.cs
		FROM (select * from zm_taskdetail where taskid='" + taskid+ "')  t1 LEFT JOIN zm_task_zuser t2 ON t1.number = t2.number LEFT JOIN zm_buildings t3 ON t1.fwbh = t3.fwbh) T LEFT JOIN  zm_pc_baselog_sqjz M ON T.fwbh = M.fwbh  and T.baseid=M.id and T.ybhnumber=M.create_by  ORDER BY M.create_on) T  where 1=1 " + strWhere + "  limit " + pageSize + " offset (" + pageIndex + " - 1) * " + pageSize;
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            string sql_count = @"select count(1) cnt from (	SELECT T.*,M.create_on yh_create_on,M.create_name yh_create_name,M.jsrysstd FROM (SELECT t1.fwbh,
			t1.taskid,
			t1.hyzt,
			t1.baseid,
			t1.ybhnumber,nickname,hzxm,address,t3.xzq,t3.town,t3.village,t3.villagedm,t1.cs
        FROM(select * from zm_taskdetail where taskid = '" + taskid+ "')  t1 LEFT JOIN zm_task_zuser t2 ON t1.number = t2.number LEFT JOIN zm_buildings t3 ON t1.fwbh = t3.fwbh) T LEFT JOIN  zm_pc_baselog_sqjz M ON T.fwbh = M.fwbh  and T.baseid=M.id and T.ybhnumber=M.create_by  ORDER BY M.create_on) T where 1 = 1 " + strWhere;
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            int count =  _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count); ; //总数量

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });
        }






        /// <summary>
        /// 删除任务情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/deteleTask")]
        public RespRet<Page<object>> deteleTask([FromQuery] string taskid, [FromQuery] string taskname)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string  sql = "select count(fwbh) from zm_taskdetail where taskid='" + taskid + "'";
            int count2 = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql);

            sql = @"delete from zm_taskdetail where taskid='"+taskid+"'";
            int count1 = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql);

            sql = @"delete from zm_task where id='"+taskid+"'";
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql); 

            return new RespRet<Page<object>>(true, "ok", new Page<object> { PageTotal = count2, Total = count2 });
        }


        /// <summary>
        /// 根据任务ID获取核验情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/getTaskCunList")]
        public RespRet<Page<object>> getTaskCunList([FromQuery] string taskid, [FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string dbzt, [FromQuery] string txtKeyWord)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (!string.IsNullOrEmpty(dbzt))
            {
                if (dbzt == "1")
                {
                    strWhere += " and REPLACE(db_pencent, '%', '')::numeric >= 98.37";
                }
                else if (dbzt == "2")
                {
                    strWhere += " and REPLACE(db_pencent, '%', '')::numeric < 98.37";
                }
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (villagedm like '%" + txtKeyWord + "%' or village like '%" + txtKeyWord + "%'   or towndm like '%" + txtKeyWord + "%' or town like '%" + txtKeyWord + "%')";
            }

            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            DateTime endData = Convert.ToDateTime(today);
            table_fwzt = "zm_building_fwzt_" + endData.Year;

            //查询上本次任务抽取时间
            string sql2 = "select create_on from zm_task where id='" + taskid + "'";
            var list2 = _dbContext.Database.GetDbConnection().Query<object>(sql2).ToList();
            string create_on = "";
            foreach (object obj in list2)
            {
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (obj != null)
                {
                    var properties = obj as IDictionary<string, object>;
                    foreach (var kvp in properties)
                    {
                        if (kvp.Key == "create_on")
                        {
                            create_on = kvp.Value.ToString();
                        }
                    }
                }
            }


            string year = DateTime.Now.Year.ToString();

            string sql = @"SELECT ROW_NUMBER( ) OVER ( ) INDEX,* FROM (select  t3.region_name xzq,t4.region_name town,t5.region_name village,t1.*,	t6.zccount,
		COALESCE(t6.twocount,0) twocount,COALESCE(t6.onecount,0)  onecount,COALESCE(t7.sycount,0) sycount,COALESCE(t7.twosycount,0) twosycount,COALESCE(t7.onesycount,0) onesycount ,COALESCE(t2.ydb,0) ydbcount,COALESCE(t2.wdb,0) wdbcount,
                           CASE WHEN hycount = 0 THEN '0.00%'   ELSE concat(CASE WHEN (round(((COALESCE(t2.ydb,0))::NUMERIC/(hycount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((COALESCE(t2.ydb,0))::NUMERIC/(hycount)::NUMERIC)*(100)::NUMERIC, 2)  END,'%') END db_pencent,
			               (SELECT string_agg(DISTINCT(u.nickname), '、') FROM zm_task_zuser u  WHERE u.number  = ANY(string_to_array(t1.jcr, '、'))) AS jcr_nicknames
                           from (SELECT DISTINCT(villagedm) villagedm,towndm,xzqdm,COUNT (fwbh)  cqcount,SUM ( CASE WHEN cs > 1 THEN 1 ELSE 0 END ) twocqcount,
	SUM ( CASE WHEN cs = 1 THEN 1 ELSE 0 END ) onecqcount,sum(case when hyzt='1' then 1 else 0 end ) hycount,sum(case when (hyzt is null) then 1 else 0 end) whycount,string_agg(distinct(number),'、') jcr FROM zm_taskdetail  WHERE taskid = '" + taskid+"'  GROUP BY villagedm,towndm,xzqdm) t1 " +
                           "left join (SELECT  xzqdm,towndm,villagedm,COUNT ( fwbh ) zccount,SUM ( CASE WHEN cs >= 2 THEN 1 ELSE 0 END ) twocount,SUM ( CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END ) onecount FROM  zm_buildings WHERE fwbh NOT IN ( SELECT DISTINCT fwbh FROM " + table_fwzt + " WHERE ( final_fwzt IS NOT NULL ) AND ( final_create_on = '"+ today + " 23:59:59' ) ) GROUP BY  xzqdm,towndm,villagedm  ) t6 ON t1.villagedm = t6.villagedm" +
                           "  left join (SELECT xzqdm,towndm, villagedm,count(fwbh) sycount, SUM(CASE WHEN cs >= 2 THEN 1 ELSE 0 END) twosycount, SUM(CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END) onesycount FROM zm_buildings  WHERE  fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on =  '"+ today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='"+ endData.Year + "' and taskid in (select id from zm_task where create_on < '"+create_on+"' )    )  GROUP BY xzqdm, towndm, villagedm) t7 ON t1.villagedm = t7.villagedm" +
                           "  left join (select village villagedm,COALESCE(sum(case when jsrysstd='是' then 1 else 0 END),0) ydb,COALESCE(sum(case when jsrysstd='否' then 1 else 0 END),0) wdb from zm_pc_baselog_sqjz  WHERE   id in (select baseid from zm_taskdetail WHERE taskid = '" + taskid + "' )   group by village,town,xzqdm) t2 on t1.villagedm=t2.villagedm left join zm_department t3 on t1.xzqdm=(t3.id)  left join zm_department t4 on concat(t1.towndm,'000')=(t4.id) left join zm_department t5 on concat(t1.villagedm)=(t5.id) )  tt where 1=1 " + strWhere+"     limit " + pageSize + " offset (" + pageIndex + " - 1) * " + pageSize;
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            string sql_count = @"select count(1) cnt from (	select  t3.region_name xzq,t4.region_name town,t5.region_name village,t1.*,	t6.zccount,
		COALESCE(t6.twocount,0) twocount,COALESCE(t6.onecount,0)  onecount,COALESCE(t7.sycount,0) sycount,COALESCE(t7.twosycount,0) twosycount,COALESCE(t7.onesycount,0) onesycount ,COALESCE(t2.ydb,0) ydbcount,COALESCE(t2.wdb,0) wdbcount,
                           CASE WHEN hycount = 0 THEN '0.00%'   ELSE concat(CASE WHEN (round(((COALESCE(t2.ydb,0))::NUMERIC/(hycount)::NUMERIC)*(100)::NUMERIC, 2) > (100)::NUMERIC) THEN 100.00  ELSE round(((COALESCE(t2.ydb,0))::NUMERIC/(hycount)::NUMERIC)*(100)::NUMERIC, 2)  END,'%') END db_pencent,
			               (SELECT string_agg(DISTINCT(u.nickname), '、') FROM zm_task_zuser u  WHERE u.number  = ANY(string_to_array(t1.jcr, '、'))) AS jcr_nicknames
                           from (SELECT DISTINCT(villagedm) villagedm,towndm,xzqdm,COUNT (fwbh)  cqcount,SUM ( CASE WHEN cs > 1 THEN 1 ELSE 0 END ) twocqcount,
	SUM ( CASE WHEN cs = 1 THEN 1 ELSE 0 END ) onecqcount,sum(case when hyzt='1' then 1 else 0 end ) hycount,sum(case when (hyzt is null) then 1 else 0 end) whycount,string_agg(distinct(number),'、') jcr FROM zm_taskdetail  WHERE taskid = '" + taskid + "'  GROUP BY villagedm,towndm,xzqdm) t1 " +
                           "left join (SELECT  xzqdm,towndm,villagedm,COUNT ( fwbh ) zccount,SUM ( CASE WHEN cs >= 2 THEN 1 ELSE 0 END ) twocount,SUM ( CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END ) onecount FROM  zm_buildings WHERE fwbh NOT IN ( SELECT DISTINCT fwbh FROM " + table_fwzt + " WHERE ( final_fwzt IS NOT NULL ) AND ( final_create_on = '" + today + " 23:59:59' ) ) GROUP BY  xzqdm,towndm,villagedm  ) t6 ON t1.villagedm = t6.villagedm" +
                           "  left join (SELECT xzqdm,towndm, villagedm,count(fwbh) sycount, SUM(CASE WHEN cs >= 2 THEN 1 ELSE 0 END) twosycount, SUM(CASE WHEN cs = 1 or cs is null or cs=0 THEN 1 ELSE 0 END) onesycount FROM zm_buildings  WHERE  fwbh not in (select distinct fwbh from " + table_fwzt + " where  (final_fwzt IS NOT NULL) and (final_create_on =  '" + today + " 23:59:59' )) and  fwbh not in (SELECT fwbh FROM zm_taskdetail where year='" + endData.Year + "' and taskid in (select id from zm_task where create_on < '" + create_on + "' )    )  GROUP BY xzqdm, towndm, villagedm) t7 ON t1.villagedm = t7.villagedm" +
                           "  left join (select village villagedm,COALESCE(sum(case when jsrysstd='是' then 1 else 0 END),0) ydb,COALESCE(sum(case when jsrysstd='否' then 1 else 0 END),0) wdb from zm_pc_baselog_sqjz  WHERE   id  in (select baseid from zm_taskdetail WHERE taskid = '" + taskid + "' )  group by village,town,xzqdm) t2 on t1.villagedm=t2.villagedm left join zm_department t3 on t1.xzqdm=(t3.id)  left join zm_department t4 on concat(t1.towndm,'000')=(t4.id) left join zm_department t5 on concat(t1.villagedm)=(t5.id))  tt  where 1=1 " + strWhere+" ";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count); ; //总数量

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("task/bhglist")]
        public RespRet<Page<object>> bhglist([FromQuery] int pageIndex, [FromQuery] int pageSize,  [FromQuery] string txtKeyWord, [FromQuery] string nickname, [FromQuery] string xzqdm,[FromQuery] string taskid)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);
            string sql = "";

            string strWhere = "  ";
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }
            if (!string.IsNullOrEmpty(nickname))
            {
                strWhere += " and (create_name  like '%" + nickname + "%')";
            }

            string year = DateTime.Now.Year.ToString();
            string sql_count = @$"select count(1) cnt from  zm_task t where 1=1  " + strWhere;
            sql = " SELECT ROW_NUMBER() OVER ()index,xzq,town,village,address,hzxm,fwbh,CONCAT_WS(',', NULLIF(x1, ''), NULLIF(x2, ''),  NULLIF(x3, ''), NULLIF(x4, ''), NULLIF(x5, '')     ) as risk_items,create_name,create_on  " +
                "FROM (    SELECT          t1.fwbh,t2.xzq,t2.towndm town,t2.villagedm village,  t2.address,t2.hzxm, t2.create_name,t2.create_on,  CASE WHEN t2.wdwhjgfjgqsyyrkrgbdj = 1   THEN '存在彩钢板建筑出租住人'  END x1,   CASE WHEN t3.sfczckfhwggptzl = '1'  THEN '存在窗口防护网、广告牌、铁栅栏'  END x2,        CASE WHEN t3.use_electric_bicycle = 1   THEN '电动自行车及蓄电池在室内停放充电'  END x3,        CASE WHEN t3.use_bottled_liquefied_gas = 1  THEN '违规使用液化石油气瓶'  END x4,        CASE WHEN t3.use_stacked_items = '1'   THEN '通道堆放杂物'  END x5    FROM (SELECT * FROM zm_taskdetail WHERE  year='" + year + "' AND taskid='" + taskid + "') t1   " +
                " LEFT JOIN zm_pc_baselog_sqjz t2 ON t1.fwbh = t2.fwbh         AND t1.baseid = t2.ID         AND t1.ybhnumber = t2.create_by    " +
                "LEFT JOIN zm_pc_yhlog_sqjz t3 ON t1.fwbh = t3.fwbh         AND t1.baseid = t3.base_id         AND t1.ybhnumber = t3.create_by) t WHERE (x1 IS NOT NULL     OR x2 IS NOT NULL     OR x3 IS NOT NULL     OR x4 IS NOT NULL     OR x5 IS NOT NULL) "+ strWhere+"   limit " + pageSize + " offset (" + pageIndex + " - 1) * " + pageSize;


            sql_count = "select count(1) from (SELECT xzq,town,village,address,hzxm,fwbh,CONCAT_WS(',', NULLIF(x1, ''), NULLIF(x2, ''),  NULLIF(x3, ''), NULLIF(x4, ''), NULLIF(x5, '')    ) as risk_items  FROM (    SELECT          t1.fwbh,t2.xzq,t2.towndm town,t2.villagedm village,  t2.address,t2.hzxm,t2.create_name,t2.create_on,        CASE WHEN t2.wdwhjgfjgqsyyrkrgbdj = 1   THEN '存在彩钢板建筑出租住人'  END x1,   CASE WHEN t3.sfczckfhwggptzl = '1'  THEN '存在窗口防护网、广告牌、铁栅栏'  END x2,        CASE WHEN t3.use_electric_bicycle = 1   THEN '电动自行车及蓄电池在室内停放充电'  END x3,        CASE WHEN t3.use_bottled_liquefied_gas = 1  THEN '违规使用液化石油气瓶'  END x4,        CASE WHEN t3.use_stacked_items = '1'   THEN '通道堆放杂物'  END x5   FROM (SELECT * FROM zm_taskdetail WHERE  year='" + year + "' AND taskid='" + taskid + "') t1    LEFT JOIN zm_pc_baselog_sqjz t2 ON t1.fwbh = t2.fwbh         AND t1.baseid = t2.ID         AND t1.ybhnumber = t2.create_by    LEFT JOIN zm_pc_yhlog_sqjz t3 ON t1.fwbh = t3.fwbh         AND t1.baseid = t3.base_id         AND t1.ybhnumber = t3.create_by) t WHERE (x1 IS NOT NULL     OR x2 IS NOT NULL     OR x3 IS NOT NULL     OR x4 IS NOT NULL     OR x5 IS NOT NULL    " +
                ") "+ strWhere + ") t1  ";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
      || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }




        /// <summary>
        /// 防火委平台乡镇台账 接口
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("cmjyxzjf/towninfo")]
        public RespRet<Page<object>> towninfo([FromQuery] string towndm, [FromQuery] string town)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            DateTime endData = Convert.ToDateTime(today);
            table_fwzt = "zm_building_fwzt_" + endData.Year;

            string sql = @"select t1.*,t2.zccount,t3.cjjccount,t4.jzjccount from (select count(distinct(fwbh)) zcount,towndm from zm_buildings where fwbh not in (select distinct(fwbh) from "+ table_fwzt + " where final_create_on='" + today + " 23:59:59' and final_fwzt is not null) and towndm='" + towndm + "' GROUP BY towndm) t1 "+

"left join "+
"(select count(distinct(fwbh)) zccount,town from zm_pc_base where fwbh not in (select distinct(fwbh) from "+ table_fwzt + " where final_create_on= '" + today + " 23:59:59' and final_fwzt is not null) and town='" + towndm + "'  GROUP BY town) t2 on t1.towndm=t2.town " +
"left join " +
"(select count(distinct(fwbh)) cjjccount,town from zm_pc_base_cj where fwbh not in (select distinct(fwbh) from "+ table_fwzt + " where final_create_on= '" + today + " 23:59:59' and final_fwzt is not null) and town='" + towndm + "' GROUP BY town) t3  on t1.towndm=t3.town  " +
"left join " +
"(select count(distinct(fwbh)) jzjccount,town from zm_pc_base_sqjz where fwbh not in (select distinct(fwbh) from "+ table_fwzt + " where final_create_on= '" + today + " 23:59:59'  and final_fwzt is not null) and town='" + towndm + "'  and yhly=4  GROUP BY town) t4 on t1.towndm=t4.town";
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list,PageTotal = list.Count, Total = list.Count });
        }

    }


    public class cuninfo 
    {
        public string xzqdm;
        public string towndm;
        public string villagedm;


        public int zcount;
        public int onecount;
        public int twocount;
        public int zccount;

        public int onesycount;
        public int twosycount;

        public List<string> fwbhs=new List<string>();
        public bool iscq = false;
    }
    public class zinfo
    {
        public string zname;
        public string number;
        public string ybhnumber;
        public string nickname;
        public List<string> assignedFwbhs = new List<string>();
    }
    public class GroupState
    {
        public zinfo Group { get; set; }
        public int CurrentHouseCount { get; set; }
        public int VillageCount { get; set; }
        public int TargetHouseCount { get; set; }
    }
}